{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import gzip"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('hosp/diagnoses_icd.csv.gz')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4756326"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df['subject_id'].isin(pt_icu[\"subject_id\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>seq_num</th>\n",
       "      <th>icd_code</th>\n",
       "      <th>icd_version</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000032</td>\n",
       "      <td>22595853</td>\n",
       "      <td>1</td>\n",
       "      <td>5723</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10000032</td>\n",
       "      <td>22595853</td>\n",
       "      <td>2</td>\n",
       "      <td>78959</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10000032</td>\n",
       "      <td>22595853</td>\n",
       "      <td>3</td>\n",
       "      <td>5715</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10000032</td>\n",
       "      <td>22595853</td>\n",
       "      <td>4</td>\n",
       "      <td>07070</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10000032</td>\n",
       "      <td>22595853</td>\n",
       "      <td>5</td>\n",
       "      <td>496</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   subject_id   hadm_id  seq_num icd_code  icd_version\n",
       "0    10000032  22595853        1     5723            9\n",
       "1    10000032  22595853        2    78959            9\n",
       "2    10000032  22595853        3     5715            9\n",
       "3    10000032  22595853        4    07070            9\n",
       "4    10000032  22595853        5      496            9"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    78959\n",
       "2     5715\n",
       "3    07070\n",
       "Name: icd_code, dtype: object"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[1:3,'icd_code']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "180640"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.subject_id.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4756326 entries, 0 to 4756325\n",
      "Data columns (total 5 columns):\n",
      " #   Column       Dtype \n",
      "---  ------       ----- \n",
      " 0   subject_id   int64 \n",
      " 1   hadm_id      int64 \n",
      " 2   seq_num      int64 \n",
      " 3   icd_code     object\n",
      " 4   icd_version  int64 \n",
      "dtypes: int64(4), object(1)\n",
      "memory usage: 181.4+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>seq_num</th>\n",
       "      <th>icd_version</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>4.756326e+06</td>\n",
       "      <td>4.756326e+06</td>\n",
       "      <td>4.756326e+06</td>\n",
       "      <td>4.756326e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1.499966e+07</td>\n",
       "      <td>2.500011e+07</td>\n",
       "      <td>8.421183e+00</td>\n",
       "      <td>9.418274e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>2.878717e+06</td>\n",
       "      <td>2.888907e+06</td>\n",
       "      <td>6.555925e+00</td>\n",
       "      <td>4.932757e-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000003e+07</td>\n",
       "      <td>2.000002e+07</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>9.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.249952e+07</td>\n",
       "      <td>2.249706e+07</td>\n",
       "      <td>3.000000e+00</td>\n",
       "      <td>9.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>1.499724e+07</td>\n",
       "      <td>2.500188e+07</td>\n",
       "      <td>7.000000e+00</td>\n",
       "      <td>9.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1.749634e+07</td>\n",
       "      <td>2.750289e+07</td>\n",
       "      <td>1.200000e+01</td>\n",
       "      <td>1.000000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1.999999e+07</td>\n",
       "      <td>2.999993e+07</td>\n",
       "      <td>3.900000e+01</td>\n",
       "      <td>1.000000e+01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         subject_id       hadm_id       seq_num   icd_version\n",
       "count  4.756326e+06  4.756326e+06  4.756326e+06  4.756326e+06\n",
       "mean   1.499966e+07  2.500011e+07  8.421183e+00  9.418274e+00\n",
       "std    2.878717e+06  2.888907e+06  6.555925e+00  4.932757e-01\n",
       "min    1.000003e+07  2.000002e+07  1.000000e+00  9.000000e+00\n",
       "25%    1.249952e+07  2.249706e+07  3.000000e+00  9.000000e+00\n",
       "50%    1.499724e+07  2.500188e+07  7.000000e+00  9.000000e+00\n",
       "75%    1.749634e+07  2.750289e+07  1.200000e+01  1.000000e+01\n",
       "max    1.999999e+07  2.999993e+07  3.900000e+01  1.000000e+01"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4756326, 5)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['5723', '78959', '5715', '07070', '496', '29680', '30981', 'V1582',\n",
       "       '07071', '2875', '2761', 'V08', '3051', '07054', 'V462', '2767',\n",
       "       '78791', '45829', '07044', '7994', 'V4986', '30500', 'G3183',\n",
       "       'F0280', 'R441', 'R296', 'E785', 'Z8546', 'R4182', 'G20', 'R609',\n",
       "       '5283', '52109', 'R1310', 'R0989', 'K31819', 'K219', 'K449',\n",
       "       'F419', 'I341', 'M810', 'Z87891', 'S72012A', 'W010XXA', 'Y93K1',\n",
       "       'Y92480', 'E7800', 'G43909', 'Z87442', 'Z7901', '9222', '920',\n",
       "       'E8854', 'E8495', '2860', '2859', '6820', '1890', 'V1201', '42789',\n",
       "       '25000', '4019', '66401', '65951', '64891', 'V270', 'V0251',\n",
       "       '8020', '41071', '5849', '7802', '7847', '41401', '28860', '79902',\n",
       "       '2724', '2720', '412', '4241', 'E8859', 'E8499', '4439', 'V5863',\n",
       "       '5712', '486', '5990', '2639', '51189', '5711', '2768', '30391',\n",
       "       '56409', '2819', '30000', '99591', '5119', '5710', '7242', '33829',\n",
       "       '29633', '29620'], dtype=object)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.icd_code.unique()[0:101]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['subject_id', 'hadm_id', 'seq_num', 'icd_code', 'icd_version'], dtype='object')"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 9, 10])"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.icd_version.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# HFrEF ICD 9"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         subject_id   hadm_id  seq_num icd_code  icd_version\n",
      "252        10000980  24947999        1    42823            9\n",
      "330        10000980  26913865        2    42823            9\n",
      "1180       10002155  23822395        3    42821            9\n",
      "1220       10002155  28994087        4    42822            9\n",
      "2626       10004235  24181354        9    42821            9\n",
      "...             ...       ...      ...      ...          ...\n",
      "4753544    19994379  21694878        3    42823            9\n",
      "4753686    19994505  22534556        2    42822            9\n",
      "4754686    19997293  20208898       18    42822            9\n",
      "4754736    19997293  27119651        2    42823            9\n",
      "4754764    19997293  28847872        4    42823            9\n",
      "\n",
      "[12622 rows x 5 columns]\n",
      "(12622, 5)\n",
      "['42823' '42821' '42822' '42820']\n",
      "subject_id      6177\n",
      "hadm_id        12578\n",
      "seq_num           21\n",
      "icd_code           4\n",
      "icd_version        1\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(df[df['icd_code'].str.startswith('4282')])\n",
    "print(df[df['icd_code'].str.startswith('4282')].shape)\n",
    "print(df[df['icd_code'].str.startswith('4282')].icd_code.unique())\n",
    "print(df[df['icd_code'].str.startswith('4282')].nunique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# HFrEF ICD 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         subject_id   hadm_id  seq_num icd_code  icd_version\n",
      "308        10000980  25911675        2    I5023           10\n",
      "355        10000980  29659838        1    I5023           10\n",
      "1494       10002430  24648311        2    I5023           10\n",
      "1516       10002430  26295318        1    I5023           10\n",
      "2498       10003637  22082422        2    I5022           10\n",
      "...             ...       ...      ...      ...          ...\n",
      "4753574    19994379  23099193        9    I5023           10\n",
      "4753608    19994379  27052619        9    I5022           10\n",
      "4753647    19994379  27334101        9    I5023           10\n",
      "4755110    19997473  27787494        3    I5023           10\n",
      "4755302    19997911  25785472        2    I5022           10\n",
      "\n",
      "[9165 rows x 5 columns]\n",
      "(9165, 5)\n",
      "['I5023' 'I5022' 'I5020' 'I5021']\n",
      "subject_id     4836\n",
      "hadm_id        9140\n",
      "seq_num          24\n",
      "icd_code          4\n",
      "icd_version       1\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(df[df['icd_code'].str.startswith('I502')])\n",
    "print(df[df['icd_code'].str.startswith('I502')].shape)\n",
    "print(df[df['icd_code'].str.startswith('I502')].icd_code.unique())\n",
    "print(df[df['icd_code'].str.startswith('I502')].nunique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# HFpEF ICD 9"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         subject_id   hadm_id  seq_num icd_code  icd_version\n",
      "279        10000980  25242409        7    42832            9\n",
      "344        10000980  29654838        1    42833            9\n",
      "631        10001877  21320596        1    42833            9\n",
      "950        10002013  21975601        2    42832            9\n",
      "970        10002013  23581541        2    42832            9\n",
      "...             ...       ...      ...      ...          ...\n",
      "4755413    19998330  23151993        1    42833            9\n",
      "4755429    19998330  24096838        2    42833            9\n",
      "4755446    19998330  24492004        2    42833            9\n",
      "4755463    19998330  27282608        2    42832            9\n",
      "4755736    19998591  24349193        5    42832            9\n",
      "\n",
      "[15515 rows x 5 columns]\n",
      "(15515, 5)\n",
      "['42832' '42833' '42830' '42831']\n",
      "subject_id      6962\n",
      "hadm_id        15447\n",
      "seq_num           24\n",
      "icd_code           4\n",
      "icd_version        1\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(df[df['icd_code'].str.startswith('4283')])\n",
    "print(df[df['icd_code'].str.startswith('4283')].shape)\n",
    "print(df[df['icd_code'].str.startswith('4283')].icd_code.unique())\n",
    "print(df[df['icd_code'].str.startswith('4283')].nunique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# HFpEF ICD 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         subject_id   hadm_id  seq_num icd_code  icd_version\n",
      "232        10000980  20897796        2    I5033           10\n",
      "574        10001667  22672901        2    I5030           10\n",
      "605        10001843  21728396        2    I5031           10\n",
      "926        10002013  21763296        6    I5032           10\n",
      "1037       10002013  25442395        7    I5032           10\n",
      "...             ...       ...      ...      ...          ...\n",
      "4749874    19986341  27575763        2    I5030           10\n",
      "4751595    19990366  24092667        2    I5030           10\n",
      "4752031    19991359  26167234        3    I5032           10\n",
      "4755096    19997448  23560173        3    I5030           10\n",
      "4755181    19997752  29452285        5    I5030           10\n",
      "\n",
      "[12423 rows x 5 columns]\n",
      "(12423, 5)\n",
      "['I5033' 'I5030' 'I5031' 'I5032']\n",
      "subject_id      6140\n",
      "hadm_id        12381\n",
      "seq_num           29\n",
      "icd_code           4\n",
      "icd_version        1\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(df[df['icd_code'].str.startswith('I503')])\n",
    "print(df[df['icd_code'].str.startswith('I503')].shape)\n",
    "print(df[df['icd_code'].str.startswith('I503')].icd_code.unique())\n",
    "print(df[df['icd_code'].str.startswith('I503')].nunique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ICD 9 and ICD 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      subject_id   hadm_id  seq_num icd_code  icd_version\n",
      "232     10000980  20897796        2    I5033           10\n",
      "252     10000980  24947999        1    42823            9\n",
      "279     10000980  25242409        7    42832            9\n",
      "308     10000980  25911675        2    I5023           10\n",
      "330     10000980  26913865        2    42823            9\n",
      "...          ...       ...      ...      ...          ...\n",
      "9211    10017851  23415994        2    42832            9\n",
      "9221    10017851  29414705        5    42832            9\n",
      "9258    10017886  26410956        2    42833            9\n",
      "9338    10018081  25973915        1    42833            9\n",
      "9390    10018328  23786647        3    I5031           10\n",
      "\n",
      "[100 rows x 5 columns]\n",
      "20189\n"
     ]
    }
   ],
   "source": [
    "mask = (df['icd_code'].str.startswith('I503')) | (df['icd_code'].str.startswith('4283')| df['icd_code'].str.startswith('4282')|df['icd_code'].str.startswith('I502'))\n",
    "result = df[mask]\n",
    "print(result.head(100))\n",
    "print(result.subject_id.nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       subject_id   hadm_id  seq_num icd_code  icd_version\n",
      "232      10000980  20897796        2    I5033           10\n",
      "279      10000980  25242409        7    42832            9\n",
      "344      10000980  29654838        1    42833            9\n",
      "574      10001667  22672901        2    I5030           10\n",
      "605      10001843  21728396        2    I5031           10\n",
      "...           ...       ...      ...      ...          ...\n",
      "21788    10043646  23469038        4    42832            9\n",
      "21809    10043646  25354589        1    42833            9\n",
      "21826    10043646  27872010        2    42832            9\n",
      "22285    10045960  24068884        3    I5033           10\n",
      "22722    10046679  28668282       10    I5032           10\n",
      "\n",
      "[100 rows x 5 columns]\n",
      "12007\n"
     ]
    }
   ],
   "source": [
    "mask = (df['icd_code'].str.startswith('I503')) | (df['icd_code'].str.startswith('4283'))\n",
    "result = df[mask]\n",
    "print(result.head(100))\n",
    "print(result.subject_id.nunique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# NTPROBNP"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_lab = pd.read_csv('hosp/labevents.csv.gz')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>labevent_id</th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>specimen_id</th>\n",
       "      <th>itemid</th>\n",
       "      <th>order_provider_id</th>\n",
       "      <th>charttime</th>\n",
       "      <th>storetime</th>\n",
       "      <th>value</th>\n",
       "      <th>valuenum</th>\n",
       "      <th>valueuom</th>\n",
       "      <th>ref_range_lower</th>\n",
       "      <th>ref_range_upper</th>\n",
       "      <th>flag</th>\n",
       "      <th>priority</th>\n",
       "      <th>comments</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>10000032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45421181</td>\n",
       "      <td>51237</td>\n",
       "      <td>P28Z0X</td>\n",
       "      <td>2180-03-23 11:51:00</td>\n",
       "      <td>2180-03-23 15:15:00</td>\n",
       "      <td>1.4</td>\n",
       "      <td>1.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.9</td>\n",
       "      <td>1.1</td>\n",
       "      <td>abnormal</td>\n",
       "      <td>ROUTINE</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>10000032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45421181</td>\n",
       "      <td>51274</td>\n",
       "      <td>P28Z0X</td>\n",
       "      <td>2180-03-23 11:51:00</td>\n",
       "      <td>2180-03-23 15:15:00</td>\n",
       "      <td>___</td>\n",
       "      <td>15.1</td>\n",
       "      <td>sec</td>\n",
       "      <td>9.4</td>\n",
       "      <td>12.5</td>\n",
       "      <td>abnormal</td>\n",
       "      <td>ROUTINE</td>\n",
       "      <td>VERIFIED.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>10000032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52958335</td>\n",
       "      <td>50853</td>\n",
       "      <td>P28Z0X</td>\n",
       "      <td>2180-03-23 11:51:00</td>\n",
       "      <td>2180-03-25 11:06:00</td>\n",
       "      <td>___</td>\n",
       "      <td>15.0</td>\n",
       "      <td>ng/mL</td>\n",
       "      <td>30.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>abnormal</td>\n",
       "      <td>ROUTINE</td>\n",
       "      <td>NEW ASSAY IN USE ___: DETECTS D2 AND D3 25-OH ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>10000032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52958335</td>\n",
       "      <td>50861</td>\n",
       "      <td>P28Z0X</td>\n",
       "      <td>2180-03-23 11:51:00</td>\n",
       "      <td>2180-03-23 16:40:00</td>\n",
       "      <td>102</td>\n",
       "      <td>102.0</td>\n",
       "      <td>IU/L</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>abnormal</td>\n",
       "      <td>ROUTINE</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>10000032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52958335</td>\n",
       "      <td>50862</td>\n",
       "      <td>P28Z0X</td>\n",
       "      <td>2180-03-23 11:51:00</td>\n",
       "      <td>2180-03-23 16:40:00</td>\n",
       "      <td>3.3</td>\n",
       "      <td>3.3</td>\n",
       "      <td>g/dL</td>\n",
       "      <td>3.5</td>\n",
       "      <td>5.2</td>\n",
       "      <td>abnormal</td>\n",
       "      <td>ROUTINE</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   labevent_id  subject_id  hadm_id  specimen_id  itemid order_provider_id  \\\n",
       "0            1    10000032      NaN     45421181   51237            P28Z0X   \n",
       "1            2    10000032      NaN     45421181   51274            P28Z0X   \n",
       "2            3    10000032      NaN     52958335   50853            P28Z0X   \n",
       "3            4    10000032      NaN     52958335   50861            P28Z0X   \n",
       "4            5    10000032      NaN     52958335   50862            P28Z0X   \n",
       "\n",
       "             charttime            storetime value  valuenum valueuom  \\\n",
       "0  2180-03-23 11:51:00  2180-03-23 15:15:00   1.4       1.4      NaN   \n",
       "1  2180-03-23 11:51:00  2180-03-23 15:15:00   ___      15.1      sec   \n",
       "2  2180-03-23 11:51:00  2180-03-25 11:06:00   ___      15.0    ng/mL   \n",
       "3  2180-03-23 11:51:00  2180-03-23 16:40:00   102     102.0     IU/L   \n",
       "4  2180-03-23 11:51:00  2180-03-23 16:40:00   3.3       3.3     g/dL   \n",
       "\n",
       "   ref_range_lower  ref_range_upper      flag priority  \\\n",
       "0              0.9              1.1  abnormal  ROUTINE   \n",
       "1              9.4             12.5  abnormal  ROUTINE   \n",
       "2             30.0             60.0  abnormal  ROUTINE   \n",
       "3              0.0             40.0  abnormal  ROUTINE   \n",
       "4              3.5              5.2  abnormal  ROUTINE   \n",
       "\n",
       "                                            comments  \n",
       "0                                                NaN  \n",
       "1                                          VERIFIED.  \n",
       "2  NEW ASSAY IN USE ___: DETECTS D2 AND D3 25-OH ...  \n",
       "3                                                NaN  \n",
       "4                                                NaN  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_lab.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_lab_id = pd.read_csv('hosp/d_labitems.csv.gz')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_lab_comp = pd.merge(df_lab, df_lab_id, on='itemid', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>labevent_id</th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>specimen_id</th>\n",
       "      <th>itemid</th>\n",
       "      <th>order_provider_id</th>\n",
       "      <th>charttime</th>\n",
       "      <th>storetime</th>\n",
       "      <th>value</th>\n",
       "      <th>valuenum</th>\n",
       "      <th>valueuom</th>\n",
       "      <th>ref_range_lower</th>\n",
       "      <th>ref_range_upper</th>\n",
       "      <th>flag</th>\n",
       "      <th>priority</th>\n",
       "      <th>comments</th>\n",
       "      <th>label</th>\n",
       "      <th>fluid</th>\n",
       "      <th>category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>10000032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45421181</td>\n",
       "      <td>51237</td>\n",
       "      <td>P28Z0X</td>\n",
       "      <td>2180-03-23 11:51:00</td>\n",
       "      <td>2180-03-23 15:15:00</td>\n",
       "      <td>1.4</td>\n",
       "      <td>1.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.9</td>\n",
       "      <td>1.1</td>\n",
       "      <td>abnormal</td>\n",
       "      <td>ROUTINE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INR(PT)</td>\n",
       "      <td>Blood</td>\n",
       "      <td>Hematology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>10000032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45421181</td>\n",
       "      <td>51274</td>\n",
       "      <td>P28Z0X</td>\n",
       "      <td>2180-03-23 11:51:00</td>\n",
       "      <td>2180-03-23 15:15:00</td>\n",
       "      <td>___</td>\n",
       "      <td>15.1</td>\n",
       "      <td>sec</td>\n",
       "      <td>9.4</td>\n",
       "      <td>12.5</td>\n",
       "      <td>abnormal</td>\n",
       "      <td>ROUTINE</td>\n",
       "      <td>VERIFIED.</td>\n",
       "      <td>PT</td>\n",
       "      <td>Blood</td>\n",
       "      <td>Hematology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>10000032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52958335</td>\n",
       "      <td>50853</td>\n",
       "      <td>P28Z0X</td>\n",
       "      <td>2180-03-23 11:51:00</td>\n",
       "      <td>2180-03-25 11:06:00</td>\n",
       "      <td>___</td>\n",
       "      <td>15.0</td>\n",
       "      <td>ng/mL</td>\n",
       "      <td>30.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>abnormal</td>\n",
       "      <td>ROUTINE</td>\n",
       "      <td>NEW ASSAY IN USE ___: DETECTS D2 AND D3 25-OH ...</td>\n",
       "      <td>25-OH Vitamin D</td>\n",
       "      <td>Blood</td>\n",
       "      <td>Chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>10000032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52958335</td>\n",
       "      <td>50861</td>\n",
       "      <td>P28Z0X</td>\n",
       "      <td>2180-03-23 11:51:00</td>\n",
       "      <td>2180-03-23 16:40:00</td>\n",
       "      <td>102</td>\n",
       "      <td>102.0</td>\n",
       "      <td>IU/L</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>abnormal</td>\n",
       "      <td>ROUTINE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alanine Aminotransferase (ALT)</td>\n",
       "      <td>Blood</td>\n",
       "      <td>Chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>10000032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52958335</td>\n",
       "      <td>50862</td>\n",
       "      <td>P28Z0X</td>\n",
       "      <td>2180-03-23 11:51:00</td>\n",
       "      <td>2180-03-23 16:40:00</td>\n",
       "      <td>3.3</td>\n",
       "      <td>3.3</td>\n",
       "      <td>g/dL</td>\n",
       "      <td>3.5</td>\n",
       "      <td>5.2</td>\n",
       "      <td>abnormal</td>\n",
       "      <td>ROUTINE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Albumin</td>\n",
       "      <td>Blood</td>\n",
       "      <td>Chemistry</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   labevent_id  subject_id  hadm_id  specimen_id  itemid order_provider_id  \\\n",
       "0            1    10000032      NaN     45421181   51237            P28Z0X   \n",
       "1            2    10000032      NaN     45421181   51274            P28Z0X   \n",
       "2            3    10000032      NaN     52958335   50853            P28Z0X   \n",
       "3            4    10000032      NaN     52958335   50861            P28Z0X   \n",
       "4            5    10000032      NaN     52958335   50862            P28Z0X   \n",
       "\n",
       "             charttime            storetime value  valuenum valueuom  \\\n",
       "0  2180-03-23 11:51:00  2180-03-23 15:15:00   1.4       1.4      NaN   \n",
       "1  2180-03-23 11:51:00  2180-03-23 15:15:00   ___      15.1      sec   \n",
       "2  2180-03-23 11:51:00  2180-03-25 11:06:00   ___      15.0    ng/mL   \n",
       "3  2180-03-23 11:51:00  2180-03-23 16:40:00   102     102.0     IU/L   \n",
       "4  2180-03-23 11:51:00  2180-03-23 16:40:00   3.3       3.3     g/dL   \n",
       "\n",
       "   ref_range_lower  ref_range_upper      flag priority  \\\n",
       "0              0.9              1.1  abnormal  ROUTINE   \n",
       "1              9.4             12.5  abnormal  ROUTINE   \n",
       "2             30.0             60.0  abnormal  ROUTINE   \n",
       "3              0.0             40.0  abnormal  ROUTINE   \n",
       "4              3.5              5.2  abnormal  ROUTINE   \n",
       "\n",
       "                                            comments  \\\n",
       "0                                                NaN   \n",
       "1                                          VERIFIED.   \n",
       "2  NEW ASSAY IN USE ___: DETECTS D2 AND D3 25-OH ...   \n",
       "3                                                NaN   \n",
       "4                                                NaN   \n",
       "\n",
       "                            label  fluid    category  \n",
       "0                         INR(PT)  Blood  Hematology  \n",
       "1                              PT  Blood  Hematology  \n",
       "2                 25-OH Vitamin D  Blood   Chemistry  \n",
       "3  Alanine Aminotransferase (ALT)  Blood   Chemistry  \n",
       "4                         Albumin  Blood   Chemistry  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_lab_comp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>labevent_id</th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>specimen_id</th>\n",
       "      <th>itemid</th>\n",
       "      <th>order_provider_id</th>\n",
       "      <th>charttime</th>\n",
       "      <th>storetime</th>\n",
       "      <th>value</th>\n",
       "      <th>valuenum</th>\n",
       "      <th>valueuom</th>\n",
       "      <th>ref_range_lower</th>\n",
       "      <th>ref_range_upper</th>\n",
       "      <th>flag</th>\n",
       "      <th>priority</th>\n",
       "      <th>comments</th>\n",
       "      <th>label</th>\n",
       "      <th>fluid</th>\n",
       "      <th>category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5090</th>\n",
       "      <td>5099</td>\n",
       "      <td>10000935</td>\n",
       "      <td>25849114.0</td>\n",
       "      <td>55494709</td>\n",
       "      <td>50963</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2187-10-11 06:25:00</td>\n",
       "      <td>2187-10-11 18:35:00</td>\n",
       "      <td>___</td>\n",
       "      <td>617.0</td>\n",
       "      <td>pg/mL</td>\n",
       "      <td>0.0</td>\n",
       "      <td>226.0</td>\n",
       "      <td>abnormal</td>\n",
       "      <td>ROUTINE</td>\n",
       "      <td>REFERENCE VALUES VARY WITH AGE, SEX, AND RENAL...</td>\n",
       "      <td>NTproBNP</td>\n",
       "      <td>Blood</td>\n",
       "      <td>Chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5917</th>\n",
       "      <td>5927</td>\n",
       "      <td>10000980</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33821244</td>\n",
       "      <td>50963</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2188-01-03 13:18:00</td>\n",
       "      <td>2188-01-03 14:42:00</td>\n",
       "      <td>___</td>\n",
       "      <td>4571.0</td>\n",
       "      <td>pg/mL</td>\n",
       "      <td>0.0</td>\n",
       "      <td>624.0</td>\n",
       "      <td>abnormal</td>\n",
       "      <td>STAT</td>\n",
       "      <td>REFERENCE VALUES VARY WITH AGE, SEX, AND RENAL...</td>\n",
       "      <td>NTproBNP</td>\n",
       "      <td>Blood</td>\n",
       "      <td>Chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6206</th>\n",
       "      <td>6216</td>\n",
       "      <td>10000980</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46753637</td>\n",
       "      <td>50963</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2189-06-27 06:48:00</td>\n",
       "      <td>2189-06-27 07:56:00</td>\n",
       "      <td>___</td>\n",
       "      <td>2826.0</td>\n",
       "      <td>pg/mL</td>\n",
       "      <td>0.0</td>\n",
       "      <td>624.0</td>\n",
       "      <td>abnormal</td>\n",
       "      <td>STAT</td>\n",
       "      <td>REFERENCE VALUES VARY WITH AGE, SEX, AND RENAL...</td>\n",
       "      <td>NTproBNP</td>\n",
       "      <td>Blood</td>\n",
       "      <td>Chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6891</th>\n",
       "      <td>6902</td>\n",
       "      <td>10000980</td>\n",
       "      <td>NaN</td>\n",
       "      <td>88477575</td>\n",
       "      <td>50963</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2190-11-06 16:00:00</td>\n",
       "      <td>2190-11-06 18:12:00</td>\n",
       "      <td>___</td>\n",
       "      <td>4279.0</td>\n",
       "      <td>pg/mL</td>\n",
       "      <td>0.0</td>\n",
       "      <td>624.0</td>\n",
       "      <td>abnormal</td>\n",
       "      <td>STAT</td>\n",
       "      <td>REFERENCE VALUES VARY WITH AGE, SEX, AND RENAL...</td>\n",
       "      <td>NTproBNP</td>\n",
       "      <td>Blood</td>\n",
       "      <td>Chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7582</th>\n",
       "      <td>7593</td>\n",
       "      <td>10000980</td>\n",
       "      <td>NaN</td>\n",
       "      <td>77976228</td>\n",
       "      <td>50963</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2191-05-30 12:40:00</td>\n",
       "      <td>2191-05-30 13:55:00</td>\n",
       "      <td>___</td>\n",
       "      <td>6440.0</td>\n",
       "      <td>pg/mL</td>\n",
       "      <td>0.0</td>\n",
       "      <td>624.0</td>\n",
       "      <td>abnormal</td>\n",
       "      <td>STAT</td>\n",
       "      <td>REFERENCE VALUES VARY WITH AGE, SEX, AND RENAL...</td>\n",
       "      <td>NTproBNP</td>\n",
       "      <td>Blood</td>\n",
       "      <td>Chemistry</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      labevent_id  subject_id     hadm_id  specimen_id  itemid  \\\n",
       "5090         5099    10000935  25849114.0     55494709   50963   \n",
       "5917         5927    10000980         NaN     33821244   50963   \n",
       "6206         6216    10000980         NaN     46753637   50963   \n",
       "6891         6902    10000980         NaN     88477575   50963   \n",
       "7582         7593    10000980         NaN     77976228   50963   \n",
       "\n",
       "     order_provider_id            charttime            storetime value  \\\n",
       "5090               NaN  2187-10-11 06:25:00  2187-10-11 18:35:00   ___   \n",
       "5917               NaN  2188-01-03 13:18:00  2188-01-03 14:42:00   ___   \n",
       "6206               NaN  2189-06-27 06:48:00  2189-06-27 07:56:00   ___   \n",
       "6891               NaN  2190-11-06 16:00:00  2190-11-06 18:12:00   ___   \n",
       "7582               NaN  2191-05-30 12:40:00  2191-05-30 13:55:00   ___   \n",
       "\n",
       "      valuenum valueuom  ref_range_lower  ref_range_upper      flag priority  \\\n",
       "5090     617.0    pg/mL              0.0            226.0  abnormal  ROUTINE   \n",
       "5917    4571.0    pg/mL              0.0            624.0  abnormal     STAT   \n",
       "6206    2826.0    pg/mL              0.0            624.0  abnormal     STAT   \n",
       "6891    4279.0    pg/mL              0.0            624.0  abnormal     STAT   \n",
       "7582    6440.0    pg/mL              0.0            624.0  abnormal     STAT   \n",
       "\n",
       "                                               comments     label  fluid  \\\n",
       "5090  REFERENCE VALUES VARY WITH AGE, SEX, AND RENAL...  NTproBNP  Blood   \n",
       "5917  REFERENCE VALUES VARY WITH AGE, SEX, AND RENAL...  NTproBNP  Blood   \n",
       "6206  REFERENCE VALUES VARY WITH AGE, SEX, AND RENAL...  NTproBNP  Blood   \n",
       "6891  REFERENCE VALUES VARY WITH AGE, SEX, AND RENAL...  NTproBNP  Blood   \n",
       "7582  REFERENCE VALUES VARY WITH AGE, SEX, AND RENAL...  NTproBNP  Blood   \n",
       "\n",
       "       category  \n",
       "5090  Chemistry  \n",
       "5917  Chemistry  \n",
       "6206  Chemistry  \n",
       "6891  Chemistry  \n",
       "7582  Chemistry  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_lab_comp[(df_lab_comp['itemid'] == 50963) & (df_lab_comp['valuenum'] > 200)].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "ntprobnp_pt = df_lab_comp[(df_lab_comp['itemid'] == 50963) & (df_lab_comp['valuenum'] > 200)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[10000935 10000980 10001401 ... 19997576 19997886 19998330]\n",
      "22018\n"
     ]
    }
   ],
   "source": [
    "print(np.unique(ntprobnp_pt.subject_id))\n",
    "print(len(np.unique(ntprobnp_pt.subject_id)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "abnormal    47321\n",
      "Name: flag, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(ntprobnp_pt.flag.value_counts())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ECHOCHARDIOGRAPHY"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_procedure = pd.read_csv('hosp/procedures_icd.csv.gz')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_procedure_id = pd.read_csv('hosp/d_icd_procedures.csv.gz')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_procedure.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_procedure_id.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_proc = pd.merge(df_procedure, df_procedure_id, on=['icd_code', 'icd_version'], how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_proc.head(20)\n",
    "df_proc.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_proc.long_title.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_proc.icd_code = df_proc.icd_code.astype(str)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "ECHOCARDIOGRAPHY ICD 9 = 37.28 ICD 10 = A57182\n",
    "\n",
    "NTPROBNP = 337626"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_proc[df_proc.icd_code.str.startswith('B24')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_proc[df_proc.long_title.str.contains('echo')].subject_id.unique()\n",
    "echo_pt_id = df_proc[df_proc.long_title.str. contains('echo')].subject_id.unique()\n",
    "print(echo_pt_id)\n",
    "print(len(echo_pt_id))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_proc.icd_version.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ICU POPULATION"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "pt_icu = pd.read_csv('icu/icustays.csv.gz')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pt_icu.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "icu_echo_nt_mask = pt_icu[pt_icu.subject_id.isin(echo_pt_id)&pt_icu.subject_id.isin(ntprobnp_pt)&pt_icu.subject_id.isin(result.subject_id.unique())].subject_id.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(icu_echo_nt_mask)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_lab_comp[df_lab_comp.subject_id.isin(icu_echo_nt_mask)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# merging ICU and other tables\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_lab_proc = pd.merge(df_proc, df_lab_comp, on=['subject_id','hadm_id'], how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "1+1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Finding EF and proBNP values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "item_list = pd.read_csv(\"icu/d_items.csv.gz\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "chart_events = pd.read_csv(\"icu/chartevents.csv.gz\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>itemid</th>\n",
       "      <th>label</th>\n",
       "      <th>abbreviation</th>\n",
       "      <th>linksto</th>\n",
       "      <th>category</th>\n",
       "      <th>unitname</th>\n",
       "      <th>param_type</th>\n",
       "      <th>lownormalvalue</th>\n",
       "      <th>highnormalvalue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>220001</td>\n",
       "      <td>Problem List</td>\n",
       "      <td>Problem List</td>\n",
       "      <td>chartevents</td>\n",
       "      <td>General</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Text</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>220003</td>\n",
       "      <td>ICU Admission date</td>\n",
       "      <td>ICU Admission date</td>\n",
       "      <td>datetimeevents</td>\n",
       "      <td>ADT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Date and time</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>220045</td>\n",
       "      <td>Heart Rate</td>\n",
       "      <td>HR</td>\n",
       "      <td>chartevents</td>\n",
       "      <td>Routine Vital Signs</td>\n",
       "      <td>bpm</td>\n",
       "      <td>Numeric</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>220046</td>\n",
       "      <td>Heart rate Alarm - High</td>\n",
       "      <td>HR Alarm - High</td>\n",
       "      <td>chartevents</td>\n",
       "      <td>Alarms</td>\n",
       "      <td>bpm</td>\n",
       "      <td>Numeric</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>220047</td>\n",
       "      <td>Heart Rate Alarm - Low</td>\n",
       "      <td>HR Alarm - Low</td>\n",
       "      <td>chartevents</td>\n",
       "      <td>Alarms</td>\n",
       "      <td>bpm</td>\n",
       "      <td>Numeric</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   itemid                    label        abbreviation         linksto  \\\n",
       "0  220001             Problem List        Problem List     chartevents   \n",
       "1  220003       ICU Admission date  ICU Admission date  datetimeevents   \n",
       "2  220045               Heart Rate                  HR     chartevents   \n",
       "3  220046  Heart rate Alarm - High     HR Alarm - High     chartevents   \n",
       "4  220047   Heart Rate Alarm - Low      HR Alarm - Low     chartevents   \n",
       "\n",
       "              category unitname     param_type  lownormalvalue  \\\n",
       "0              General      NaN           Text             NaN   \n",
       "1                  ADT      NaN  Date and time             NaN   \n",
       "2  Routine Vital Signs      bpm        Numeric             NaN   \n",
       "3               Alarms      bpm        Numeric             NaN   \n",
       "4               Alarms      bpm        Numeric             NaN   \n",
       "\n",
       "   highnormalvalue  \n",
       "0              NaN  \n",
       "1              NaN  \n",
       "2              NaN  \n",
       "3              NaN  \n",
       "4              NaN  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "item_list.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>stay_id</th>\n",
       "      <th>caregiver_id</th>\n",
       "      <th>charttime</th>\n",
       "      <th>storetime</th>\n",
       "      <th>itemid</th>\n",
       "      <th>value</th>\n",
       "      <th>valuenum</th>\n",
       "      <th>valueuom</th>\n",
       "      <th>warning</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000032</td>\n",
       "      <td>29079034</td>\n",
       "      <td>39553978</td>\n",
       "      <td>47007.0</td>\n",
       "      <td>2180-07-23 21:01:00</td>\n",
       "      <td>2180-07-23 22:15:00</td>\n",
       "      <td>220179</td>\n",
       "      <td>82</td>\n",
       "      <td>82.0</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10000032</td>\n",
       "      <td>29079034</td>\n",
       "      <td>39553978</td>\n",
       "      <td>47007.0</td>\n",
       "      <td>2180-07-23 21:01:00</td>\n",
       "      <td>2180-07-23 22:15:00</td>\n",
       "      <td>220180</td>\n",
       "      <td>59</td>\n",
       "      <td>59.0</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10000032</td>\n",
       "      <td>29079034</td>\n",
       "      <td>39553978</td>\n",
       "      <td>47007.0</td>\n",
       "      <td>2180-07-23 21:01:00</td>\n",
       "      <td>2180-07-23 22:15:00</td>\n",
       "      <td>220181</td>\n",
       "      <td>63</td>\n",
       "      <td>63.0</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10000032</td>\n",
       "      <td>29079034</td>\n",
       "      <td>39553978</td>\n",
       "      <td>47007.0</td>\n",
       "      <td>2180-07-23 22:00:00</td>\n",
       "      <td>2180-07-23 22:15:00</td>\n",
       "      <td>220045</td>\n",
       "      <td>94</td>\n",
       "      <td>94.0</td>\n",
       "      <td>bpm</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10000032</td>\n",
       "      <td>29079034</td>\n",
       "      <td>39553978</td>\n",
       "      <td>47007.0</td>\n",
       "      <td>2180-07-23 22:00:00</td>\n",
       "      <td>2180-07-23 22:15:00</td>\n",
       "      <td>220179</td>\n",
       "      <td>85</td>\n",
       "      <td>85.0</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   subject_id   hadm_id   stay_id  caregiver_id            charttime  \\\n",
       "0    10000032  29079034  39553978       47007.0  2180-07-23 21:01:00   \n",
       "1    10000032  29079034  39553978       47007.0  2180-07-23 21:01:00   \n",
       "2    10000032  29079034  39553978       47007.0  2180-07-23 21:01:00   \n",
       "3    10000032  29079034  39553978       47007.0  2180-07-23 22:00:00   \n",
       "4    10000032  29079034  39553978       47007.0  2180-07-23 22:00:00   \n",
       "\n",
       "             storetime  itemid value  valuenum valueuom  warning  \n",
       "0  2180-07-23 22:15:00  220179    82      82.0     mmHg      0.0  \n",
       "1  2180-07-23 22:15:00  220180    59      59.0     mmHg      0.0  \n",
       "2  2180-07-23 22:15:00  220181    63      63.0     mmHg      0.0  \n",
       "3  2180-07-23 22:15:00  220045    94      94.0      bpm      0.0  \n",
       "4  2180-07-23 22:15:00  220179    85      85.0     mmHg      0.0  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chart_events.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(313645063, 11)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chart_events.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>itemid</th>\n",
       "      <th>label</th>\n",
       "      <th>abbreviation</th>\n",
       "      <th>linksto</th>\n",
       "      <th>category</th>\n",
       "      <th>unitname</th>\n",
       "      <th>param_type</th>\n",
       "      <th>lownormalvalue</th>\n",
       "      <th>highnormalvalue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>220059</td>\n",
       "      <td>Pulmonary Artery Pressure systolic</td>\n",
       "      <td>PAPs</td>\n",
       "      <td>chartevents</td>\n",
       "      <td>Hemodynamics</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>Numeric</td>\n",
       "      <td>15.0</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>220060</td>\n",
       "      <td>Pulmonary Artery Pressure diastolic</td>\n",
       "      <td>PAPd</td>\n",
       "      <td>chartevents</td>\n",
       "      <td>Hemodynamics</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>Numeric</td>\n",
       "      <td>8.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>220061</td>\n",
       "      <td>Pulmonary Artery Pressure mean</td>\n",
       "      <td>PAPm</td>\n",
       "      <td>chartevents</td>\n",
       "      <td>Hemodynamics</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>Numeric</td>\n",
       "      <td>10.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>220063</td>\n",
       "      <td>Pulmonary Artery Pressure Alarm - High</td>\n",
       "      <td>PAP Alarm - High</td>\n",
       "      <td>chartevents</td>\n",
       "      <td>Alarms</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>Numeric</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>220066</td>\n",
       "      <td>Pulmonary Artery Pressure Alarm - Low</td>\n",
       "      <td>PAP Alarm - Low</td>\n",
       "      <td>chartevents</td>\n",
       "      <td>Alarms</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>Numeric</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    itemid                                   label      abbreviation  \\\n",
       "11  220059      Pulmonary Artery Pressure systolic              PAPs   \n",
       "12  220060     Pulmonary Artery Pressure diastolic              PAPd   \n",
       "13  220061          Pulmonary Artery Pressure mean              PAPm   \n",
       "14  220063  Pulmonary Artery Pressure Alarm - High  PAP Alarm - High   \n",
       "15  220066   Pulmonary Artery Pressure Alarm - Low   PAP Alarm - Low   \n",
       "\n",
       "        linksto      category unitname param_type  lownormalvalue  \\\n",
       "11  chartevents  Hemodynamics     mmHg    Numeric            15.0   \n",
       "12  chartevents  Hemodynamics     mmHg    Numeric             8.0   \n",
       "13  chartevents  Hemodynamics     mmHg    Numeric            10.0   \n",
       "14  chartevents        Alarms     mmHg    Numeric             NaN   \n",
       "15  chartevents        Alarms     mmHg    Numeric             NaN   \n",
       "\n",
       "    highnormalvalue  \n",
       "11             25.0  \n",
       "12             15.0  \n",
       "13             20.0  \n",
       "14              NaN  \n",
       "15              NaN  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "item_list[item_list.label.str.lower().str.contains('pulmonary artery')].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>itemid</th>\n",
       "      <th>label</th>\n",
       "      <th>abbreviation</th>\n",
       "      <th>linksto</th>\n",
       "      <th>category</th>\n",
       "      <th>unitname</th>\n",
       "      <th>param_type</th>\n",
       "      <th>lownormalvalue</th>\n",
       "      <th>highnormalvalue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [itemid, label, abbreviation, linksto, category, unitname, param_type, lownormalvalue, highnormalvalue]\n",
       "Index: []"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "item_list[item_list.label.str.lower().str.contains('')].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>stay_id</th>\n",
       "      <th>caregiver_id</th>\n",
       "      <th>charttime</th>\n",
       "      <th>storetime</th>\n",
       "      <th>itemid</th>\n",
       "      <th>value</th>\n",
       "      <th>valuenum</th>\n",
       "      <th>valueuom</th>\n",
       "      <th>warning</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [subject_id, hadm_id, stay_id, caregiver_id, charttime, storetime, itemid, value, valuenum, valueuom, warning]\n",
       "Index: []"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chart_events[chart_events['itemid']==227008].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sum(chart_events['itemid']==227008)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>itemid</th>\n",
       "      <th>label</th>\n",
       "      <th>abbreviation</th>\n",
       "      <th>linksto</th>\n",
       "      <th>category</th>\n",
       "      <th>unitname</th>\n",
       "      <th>param_type</th>\n",
       "      <th>lownormalvalue</th>\n",
       "      <th>highnormalvalue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>220179</td>\n",
       "      <td>Non Invasive Blood Pressure systolic</td>\n",
       "      <td>NBPs</td>\n",
       "      <td>chartevents</td>\n",
       "      <td>Routine Vital Signs</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>Numeric</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    itemid                                 label abbreviation      linksto  \\\n",
       "24  220179  Non Invasive Blood Pressure systolic         NBPs  chartevents   \n",
       "\n",
       "               category unitname param_type  lownormalvalue  highnormalvalue  \n",
       "24  Routine Vital Signs     mmHg    Numeric             NaN              NaN  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "item_list[item_list['itemid']==220179]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>stay_id</th>\n",
       "      <th>caregiver_id</th>\n",
       "      <th>charttime</th>\n",
       "      <th>storetime</th>\n",
       "      <th>itemid</th>\n",
       "      <th>value</th>\n",
       "      <th>valuenum</th>\n",
       "      <th>valueuom</th>\n",
       "      <th>warning</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000032</td>\n",
       "      <td>29079034</td>\n",
       "      <td>39553978</td>\n",
       "      <td>47007.0</td>\n",
       "      <td>2180-07-23 21:01:00</td>\n",
       "      <td>2180-07-23 22:15:00</td>\n",
       "      <td>220179</td>\n",
       "      <td>82</td>\n",
       "      <td>82.0</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10000032</td>\n",
       "      <td>29079034</td>\n",
       "      <td>39553978</td>\n",
       "      <td>47007.0</td>\n",
       "      <td>2180-07-23 22:00:00</td>\n",
       "      <td>2180-07-23 22:15:00</td>\n",
       "      <td>220179</td>\n",
       "      <td>85</td>\n",
       "      <td>85.0</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10000032</td>\n",
       "      <td>29079034</td>\n",
       "      <td>39553978</td>\n",
       "      <td>66056.0</td>\n",
       "      <td>2180-07-23 19:00:00</td>\n",
       "      <td>2180-07-23 19:59:00</td>\n",
       "      <td>220179</td>\n",
       "      <td>93</td>\n",
       "      <td>93.0</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>10000032</td>\n",
       "      <td>29079034</td>\n",
       "      <td>39553978</td>\n",
       "      <td>66056.0</td>\n",
       "      <td>2180-07-23 20:00:00</td>\n",
       "      <td>2180-07-23 21:01:00</td>\n",
       "      <td>220179</td>\n",
       "      <td>90</td>\n",
       "      <td>90.0</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163</th>\n",
       "      <td>10000032</td>\n",
       "      <td>29079034</td>\n",
       "      <td>39553978</td>\n",
       "      <td>88981.0</td>\n",
       "      <td>2180-07-23 14:11:00</td>\n",
       "      <td>2180-07-23 14:17:00</td>\n",
       "      <td>220179</td>\n",
       "      <td>84</td>\n",
       "      <td>84.0</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     subject_id   hadm_id   stay_id  caregiver_id            charttime  \\\n",
       "0      10000032  29079034  39553978       47007.0  2180-07-23 21:01:00   \n",
       "4      10000032  29079034  39553978       47007.0  2180-07-23 22:00:00   \n",
       "10     10000032  29079034  39553978       66056.0  2180-07-23 19:00:00   \n",
       "21     10000032  29079034  39553978       66056.0  2180-07-23 20:00:00   \n",
       "163    10000032  29079034  39553978       88981.0  2180-07-23 14:11:00   \n",
       "\n",
       "               storetime  itemid value  valuenum valueuom  warning  \n",
       "0    2180-07-23 22:15:00  220179    82      82.0     mmHg      0.0  \n",
       "4    2180-07-23 22:15:00  220179    85      85.0     mmHg      0.0  \n",
       "10   2180-07-23 19:59:00  220179    93      93.0     mmHg      0.0  \n",
       "21   2180-07-23 21:01:00  220179    90      90.0     mmHg      0.0  \n",
       "163  2180-07-23 14:17:00  220179    84      84.0     mmHg      0.0  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chart_events[chart_events['itemid']==220179].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sum(chart_events['itemid']==227179)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "chart_with_item_name = pd.merge(chart_events, item_list[['itemid', 'label']], on=['itemid'], how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "head(chart_with_item_name)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
